package com.example.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.example.entity.DailySalesStatistics;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;

/**
 * Created by Administrator on 2019/5/29.
 */
public interface DailySalesStatisticsRepository extends JpaRepository<DailySalesStatistics, Long>,JpaSpecificationExecutor<DailySalesStatistics> {

    //查询当月销售数量
//    @Query(value="select sum(order_amount) from daily_sales_statistics where DATE_FORMAT(statistical_time,'%Y-%m')= ?1",nativeQuery = true)
    @Query(value="select IFNULL(sum(count),0) from machine_combo_order where DATE_FORMAT(pay_time,\"%Y-%m\") =?1 and order_status=5",nativeQuery = true)
    int selectMonthSale(String month);
//    @Query(value="select sum(order_amount) from daily_sales_statistics where DATE_FORMAT(statistical_time,'%Y-%d') = ?1")
//    int queryMonthCount(String month);

    //根据设备类型和时间，查询出库数量
    @Query(value = "select COUNT(DISTINCT(stockout.imei)) from stockout LEFT JOIN machine ON stockout.imei = machine.imei WHERE DATE_FORMAT(stockout.create_time,?1) = ?2 AND machine.product_type = ?3",nativeQuery = true)
    int selectStockOutByDateAndProductType(String format,String date,Integer productType);

    //据设备类型，查询当前库存
    @Query(value = "select count(1) from machine where machine.`status`=?1 and product_type=?2",nativeQuery = true)
    int selectStockByProductType(Integer status,Integer productType);

    //根据设备类型和时间，查询入库数量
    @Query(value = "select COUNT(stockin.imei) from stockin left join machine on stockin.imei = machine.imei where DATE_FORMAT(stockin.create_time,?1) = ?2 and machine.product_type = ?3",nativeQuery = true)
    int selectStockInByDateAndProductType(String format,String date,Integer productType);

    //根据设备类型和时间，查询订单数量
    @Query(value="select IFNULL(SUM(machine_combo_order_detail.count),0) from machine_combo_order left join machine_combo_order_detail on machine_combo_order_detail.order_no = machine_combo_order.order_no "+
            "where machine_combo_order_detail.product_id = ?3 and DATE_FORMAT(machine_combo_order.create_time,?1) = ?2 and machine_combo_order.rec_status = 0 and machine_combo_order.order_status = 5",nativeQuery = true)
    int selectOrderAmountByDateAndProductType(String format,String date,Integer productType);

}
